{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "toc": true
   },
   "source": [
    "<h1>目录<span class=\"tocSkip\"></span></h1>\n",
    "<div class=\"toc\"><ul class=\"toc-item\"><li><span><a href=\"#avg\" data-toc-modified-id=\"avg-1\"><span class=\"toc-item-num\">1&nbsp;&nbsp;</span>avg</a></span></li><li><span><a href=\"#sum\" data-toc-modified-id=\"sum-2\"><span class=\"toc-item-num\">2&nbsp;&nbsp;</span>sum</a></span></li><li><span><a href=\"#count\" data-toc-modified-id=\"count-3\"><span class=\"toc-item-num\">3&nbsp;&nbsp;</span>count</a></span></li><li><span><a href=\"#max\" data-toc-modified-id=\"max-4\"><span class=\"toc-item-num\">4&nbsp;&nbsp;</span>max</a></span></li><li><span><a href=\"#聚集不同值\" data-toc-modified-id=\"聚集不同值-5\"><span class=\"toc-item-num\">5&nbsp;&nbsp;</span>聚集不同值</a></span></li><li><span><a href=\"#组合聚合函数\" data-toc-modified-id=\"组合聚合函数-6\"><span class=\"toc-item-num\">6&nbsp;&nbsp;</span>组合聚合函数</a></span></li></ul></div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "import mysql.connector\n",
    "import pandas as pd\n",
    "conn = mysql.connector.connect(host='127.0.0.1',\n",
    "                      user='iroan',\n",
    "                      password='iroanMYS47',\n",
    "                      database = 'practice_sql')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>prod_id</th>\n",
       "      <th>vend_id</th>\n",
       "      <th>prod_name</th>\n",
       "      <th>prod_price</th>\n",
       "      <th>prod_desc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BNBG01</td>\n",
       "      <td>DLL01</td>\n",
       "      <td>Fish bean bag toy</td>\n",
       "      <td>3.49</td>\n",
       "      <td>Fish bean bag toy, complete with bean bag worm...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BNBG02</td>\n",
       "      <td>DLL01</td>\n",
       "      <td>Bird bean bag toy</td>\n",
       "      <td>3.49</td>\n",
       "      <td>Bird bean bag toy, eggs are not included</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BNBG03</td>\n",
       "      <td>DLL01</td>\n",
       "      <td>Rabbit bean bag toy</td>\n",
       "      <td>3.49</td>\n",
       "      <td>Rabbit bean bag toy, comes with bean bag carrots</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BR01</td>\n",
       "      <td>BRS01</td>\n",
       "      <td>8 inch teddy bear</td>\n",
       "      <td>5.99</td>\n",
       "      <td>8 inch teddy bear, comes with cap and jacket</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BR02</td>\n",
       "      <td>BRS01</td>\n",
       "      <td>12 inch teddy bear</td>\n",
       "      <td>8.99</td>\n",
       "      <td>12 inch teddy bear, comes with cap and jacket</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>BR03</td>\n",
       "      <td>BRS01</td>\n",
       "      <td>18 inch teddy bear</td>\n",
       "      <td>11.99</td>\n",
       "      <td>18 inch teddy bear, comes with cap and jacket</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>RGAN01</td>\n",
       "      <td>DLL01</td>\n",
       "      <td>Raggedy Ann</td>\n",
       "      <td>4.99</td>\n",
       "      <td>18 inch Raggedy Ann doll</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>RYL01</td>\n",
       "      <td>FNG01</td>\n",
       "      <td>King doll</td>\n",
       "      <td>9.49</td>\n",
       "      <td>12 inch king doll with royal garments and crown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>RYL02</td>\n",
       "      <td>FNG01</td>\n",
       "      <td>Queen doll</td>\n",
       "      <td>9.49</td>\n",
       "      <td>12 inch queen doll with royal garments and crown</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  prod_id vend_id            prod_name  prod_price  \\\n",
       "0  BNBG01   DLL01    Fish bean bag toy        3.49   \n",
       "1  BNBG02   DLL01    Bird bean bag toy        3.49   \n",
       "2  BNBG03   DLL01  Rabbit bean bag toy        3.49   \n",
       "3    BR01   BRS01    8 inch teddy bear        5.99   \n",
       "4    BR02   BRS01   12 inch teddy bear        8.99   \n",
       "5    BR03   BRS01   18 inch teddy bear       11.99   \n",
       "6  RGAN01   DLL01          Raggedy Ann        4.99   \n",
       "7   RYL01   FNG01            King doll        9.49   \n",
       "8   RYL02   FNG01           Queen doll        9.49   \n",
       "\n",
       "                                           prod_desc  \n",
       "0  Fish bean bag toy, complete with bean bag worm...  \n",
       "1           Bird bean bag toy, eggs are not included  \n",
       "2   Rabbit bean bag toy, comes with bean bag carrots  \n",
       "3       8 inch teddy bear, comes with cap and jacket  \n",
       "4      12 inch teddy bear, comes with cap and jacket  \n",
       "5      18 inch teddy bear, comes with cap and jacket  \n",
       "6                           18 inch Raggedy Ann doll  \n",
       "7    12 inch king doll with royal garments and crown  \n",
       "8   12 inch queen doll with royal garments and crown  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\n",
    "'''select * from Products;'''\n",
    ",conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# avg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>b'avg_price'</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6.823333</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   b'avg_price'\n",
       "0      6.823333"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\n",
    "'''select avg(prod_price) as avg_price from Products;'''\n",
    ",conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>b'avg_price'</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3.865</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   b'avg_price'\n",
       "0         3.865"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\n",
    "'''select avg(prod_price) as avg_price from Products where vend_id = 'DLL01';'''\n",
    ",conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# sum"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>b'sum_price'</th>\n",
       "      <th>b'avg_price'</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>61.41</td>\n",
       "      <td>6.823333</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   b'sum_price'  b'avg_price'\n",
       "0         61.41      6.823333"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\n",
    "'''select sum(prod_price) as sum_price ,avg(prod_price) as avg_price from Products;'''\n",
    ",conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>b'num_cust'</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   b'num_cust'\n",
       "0            5"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\n",
    "'''select count(*) as num_cust from Customers;'''\n",
    ",conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>b'num_cust'</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   b'num_cust'\n",
       "0            3"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\n",
    "'''select count(cust_email) as num_cust from Customers;'''\n",
    ",conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# max"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>b'max_price'</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11.99</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   b'max_price'\n",
       "0         11.99"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\n",
    "'''select max(prod_price) as max_price from Products;'''\n",
    ",conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 聚集不同值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>b'avg_price'</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4.24</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   b'avg_price'\n",
       "0          4.24"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\n",
    "'''select avg(distinct prod_price) as avg_price from Products where vend_id = 'DLL01';'''\n",
    ",conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 组合聚合函数"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "base"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {
    "height": "12px",
    "width": "163px"
   },
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "目录",
   "title_sidebar": "大纲",
   "toc_cell": true,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
